<%@page import="Configurations.FileOperations"%>
<%@page import="Configurations.OTSFile"%>
<%@page import="java.util.ArrayList"%>
<%@page import="Configurations.AYFile"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="Configurations.MySQL"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Connection"%>
<%@page import="Configurations.Application"%>
<%@page import="Configurations.ApplicationList"%>
<%
    session.setAttribute("displayPage", "application");
    // Redirect if this page is being displayed by itself
    if (request.getServletPath().equals("/application.jsp")) {
        System.out.println("Current Page is set to application. Redirect to index.jsp");
        response.sendRedirect("");
    }
    // Store the email(key) of the application that the user wants to view
    String email = (String) session.getAttribute("appRequest");
%>
<h3>Application Information</h3>
<% if ((Boolean) session.getAttribute("isChair")) {
        Connection con2 = null;
        ResultSet rs2 = null;
        PreparedStatement ps2 = null;
        // Assign current application to specified user
        if (request.getParameter("username") != null) {
            try {
                Class.forName(MySQL.DB_DRIVER);
                con2 = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_USER, MySQL.DB_PASS);
                // Store current username
                String selectedUser = request.getParameter("username");
                String sql3 = "SELECT * FROM " + MySQL.DB_LOGIN + " WHERE " + MySQL.DB_LOGIN_USERNAME + "=?";
                ps2 = con2.prepareStatement(sql3);
                ps2.setString(1, selectedUser);
                rs2 = ps2.executeQuery();
                if (rs2.next()) {
                    String assigned = rs2.getString(MySQL.DB_LOGIN_ASSIGNED);
                    if (assigned == null) {
                        assigned = "";
                    }
                    String numReviews = "" + (rs2.getInt(MySQL.DB_LOGIN_NUMREVIEWS) + 1);
                    // Append assigned list if it is a new email
                    boolean duplicate = false;
                    if (assigned.indexOf(email) == -1) {
                        assigned = assigned + "," + email;
                    } else {
                        duplicate = true;
                        numReviews = "" + (Integer.parseInt(numReviews) - 1);
                    }
                    sql3 = "UPDATE " + MySQL.DB_LOGIN + " SET " + MySQL.DB_LOGIN_ASSIGNED + "=?," + MySQL.DB_LOGIN_NUMREVIEWS + "=? WHERE " + MySQL.DB_LOGIN_USERNAME + "=?";
                    ps2 = con2.prepareStatement(sql3);
                    ps2.setString(1, assigned);
                    ps2.setString(2, numReviews);
                    ps2.setString(3, selectedUser);
                    ps2.execute();
                    System.out.println(sql3);
%>
<div id="content">
    <%if (!duplicate) {%>
    <h4>Application with email <%=email%> has been assigned to <%=selectedUser%>.</h4>
    <%} else {
    %>
    <h4><%=selectedUser%> has already been assigned this application.</h4>
    <%            }
    %></div><br/>
    <%
                }
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("Application assignment: Failure");
            }
        }
        try {
            Class.forName(MySQL.DB_DRIVER);
            con2 = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_USER, MySQL.DB_PASS);
            // Only get avaialable users
            String sql2 = "SELECT * FROM " + MySQL.DB_LOGIN + " WHERE " + MySQL.DB_LOGIN_AVAILABLE + "=1";
            ps2 = con2.prepareStatement(sql2);
            rs2 = ps2.executeQuery();
            if (rs2.next()) {
                System.out.println("Query for user list: Success");
    %>
    <%
        // Tell the user the changes have been saved
        if (session.getAttribute("changes") != null) {
    %>
<div id="content"><h4>Application changes have been saved.</h4></div><br/>
<%
        session.setAttribute("changes", null);
    }
%>
<%
    // Tell the user the review have been saved
    if (session.getAttribute("saveReview") != null) {
        if (session.getAttribute("saveReview").equals("saved")) {
%>
<div id="content"><h4>Review has been saved.</h4></div><br/>
<%        } else {
%>
<div id="content"><h4>Review was not saved. Make sure your rating is a number.</h4></div><br/>
<%        }
        session.setAttribute("saveReview", null);
    }
%>
<form>
    <div align ="center" id="content">
        <h4>Assign Application to a User</h4>
        <table border="1">
            <tr align="center">
                <td width="4%"></td>
                <td width="16%"><b>Username</b></td>
                <td width="16%"><b>Email</b></td>
                <td width="16%"><b>Reviews This Year</b></td>
                <td width="16%"><b>Workload Ratio</b></td>
                <td width="16%"><b>Countries of Expertise</b></td>
                <td width="16%"><b>Fields of Expertise</b></td>
            </tr>
            <%do {
            %>
            <tr align="center">
                <td><input type="radio" name="username" value=<%=rs2.getString(MySQL.DB_LOGIN_USERNAME)%> checked/></td>
                <td><%=rs2.getString(MySQL.DB_LOGIN_USERNAME)%></td>
                <td><%=rs2.getString(MySQL.DB_LOGIN_EMAIL)%></td>
                <td><%=rs2.getString(MySQL.DB_LOGIN_NUMREVIEWS)%></td>
                <td><%=rs2.getString(MySQL.DB_LOGIN_WORKLOAD)%></td>
                <td><%=rs2.getString(MySQL.DB_LOGIN_CNTRY)%></td>
                <td><%=rs2.getString(MySQL.DB_LOGIN_RSRCH)%></td>
            </tr>
            <%} while (rs2.next());%>
        </table>
        <input type="submit" name="Assign" value="Assign"/>    
    </div>
</form>
<form>
    <div id="content">
        <h4>Assign a Tier</h4>
        -1: initial value, indicating that tier has not been assigned yet<br/>
        0.0-1.0: Admit<br/>
        1.1-1.4: Very likely admit; need interview or additional documents<br/>
        1.5-1.9: Borderline; need additional review<br/>
        2.0-2.9: Likely reject, but can be revived<br/>
        3.0-6.0: Reject<br/>
        <input type="text" name="tier" value="" />
        <input type="submit" name="button" value="Submit"/>
    </div>
</form>
<% }
        } catch (Exception e) {
            System.out.println("Error establishing connection to MySQL database");
        } finally {
            try {
                rs2.close();
                ps2.close();
                con2.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }%>
<%
    // Tell the user the review have been saved
    if (session.getAttribute("saveReview") != null) {
        if (session.getAttribute("saveReview").equals("saved")) {
%>
<div id="content"><h4>Review has been saved.</h4></div><br/>
<%        } else {
%>
<div id="content"><h4>Review was not saved. Make sure your rating is a number.</h4></div><br/>
<%        }
        session.setAttribute("saveReview", null);
    }
%>
<%// Should only display if assigned
    Connection testCon = null;
    ResultSet testSet = null;
    PreparedStatement testStat = null;
    Class.forName(MySQL.DB_DRIVER);
    testCon = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_USER, MySQL.DB_PASS);
    String testSQL = "SELECT * FROM " + MySQL.DB_LOGIN + " WHERE " + MySQL.DB_LOGIN_USERNAME + "=?";
    testStat = testCon.prepareStatement(testSQL);
    testStat.setString(1, (String) session.getAttribute("username"));
    testSet = testStat.executeQuery();
    boolean show = false;
    if (testSet.next()) {
        String list = testSet.getString(MySQL.DB_LOGIN_ASSIGNED);
        if (list.indexOf(email) != -1) {
            show = true;
        }
    }
    if (show) {
%>
<form action="saveReview.jsp" method="post">
    <div id="content">
        <h4>Rate and Review</h4>
        Enter your numerical rating: <br/>
        <input type="text" name="rating"/>
        <br/>
        Enter your comments:<br/>
        <textarea name="comments" cols="90" rows="5"></textarea>
        <br/>
        <input type="hidden" value="<%=email%>" name="appEmail" />
        <input type="submit" value="Submit"/>
    </div>
</form>
<%}%>
<%
// Retrieve information for specified user, assumes each user has a unique email
// Application selection = ApplicationList.getInstance().get(email);
    Connection con = null;
    ResultSet rs = null;
    PreparedStatement ps = null;
    Class.forName(MySQL.DB_DRIVER);
    try {
        Class.forName(MySQL.DB_DRIVER);
        con = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_USER, MySQL.DB_PASS);
        String sql = "SELECT * FROM " + MySQL.DB_APPS + " WHERE " + MySQL.DB_APPS_EMAIL + "=?";
        ps = con.prepareStatement(sql);
        ps.setString(1, email);
        rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("Query for specific application from applications: Successful");
            ArrayList<String> fieldOrder = AYFile.getAYFieldOrder();
%>
<form action="oldReviews.jsp" method="post">
    <div id="content">
        <h4>View Previous Reviews</h4>
        <input type="hidden" value="<%=email%>" name="appEmail2" />
        <input type="submit" value="View" />
    </div>
</form>
<form action="saveChanges.jsp" method="post">
    <div id="content">
        <h4>Download a PDF version</h4>
        <%
            if (FileOperations.fileExist(email + ".pdf")) {
                out.println("<a href=\"" + "../" + email + ".pdf" + "\">" + "Download" + "</a><br/>");
            }
        %>
    </div>
    <br />
    <div id="content">
        <h4>ApplyYourself Information</h4>
        <table border="1" class="applicationTable">    
            <%
                for (int i = 0; i < fieldOrder.size(); i++) {
            %>
            <tr>
                <td><b>
                        <%=fieldOrder.get(i)%>
                    </b></td>
                <td>
                    <% if ((Boolean) session.getAttribute("isChair") == true) {%>
                    <input type="text" size ="50" name="<%=fieldOrder.get(i)%>" value="<%=rs.getString(fieldOrder.get(i))%>"/>
                    <% } else {%>
                    <%=rs.getString(fieldOrder.get(i))%>
                    <% }%>
                </td>
            </tr>
            <%            }
            %>
        </table>
    </div>
    <br/>
    <%
        sql = "SELECT * FROM " + MySQL.DB_SCORES + " WHERE " + MySQL.DB_SCORES_EMAIL + "=?";
        ps = con.prepareStatement(sql);
        ps.setString(1, email);
        rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("Query for specific application from scores: Successful");
            fieldOrder = OTSFile.getOTSFieldOrder();
    %>
    <div id="content">
        <h4>Official Test Score Information</h4>
        <table border="1" class="applicationTable">
            <%
                for (int i = 0; i < fieldOrder.size(); i++) {
            %>
            <tr>
                <td><b>
                        <%=fieldOrder.get(i)%>
                    </b></td>
                <td>
                    <% if ((Boolean) session.getAttribute("isChair") == true) {%>
                    <input type="text" size ="50" name="<%=fieldOrder.get(i)%>" value="<%=rs.getString(fieldOrder.get(i))%>"/>
                    <% } else {%>
                    <%=rs.getString(fieldOrder.get(i))%>
                    <% }%>
                </td>
            </tr>
            <% }%>
        </table>
    </div>
    <br/>
    <div id="content">
        <% if ((Boolean) session.getAttribute("isChair") == true) {%>
        <input type="submit" name="saveChanges" value="Save Changes"/>
        <% }%>
    </div>
</form>
<br/>
<%    }
%>
<%
        }
    } catch (Exception e) {
        System.out.println("Error establishing connection to MySQL database.");
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            ps.close();
            con.close();
        } catch (Exception e2) {
            e2.printStackTrace();
        }
    }
%>